/******************************************************************************
 * @File name   :      MaintainDealerDaoImpl.java
 *
 * @Author      :      ANMA
 *
 * @Date        :      Jul 15, 2015
 *
 * @Copyright Notice: 
 * Copyright (c) 2014 DMS, Inc. All  Rights Reserved.
 * This software is published under the terms of the DMS Software
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file.
 * 
 * ----------------------------------------------------------------------------
 * Date                         Who         Version         Comments
 * Jul 15, 2015 4:03:08 PM    ANMA      1.0            Initial Version
 *****************************************************************************/
package com.cap.bts.crmdp.dealer.masterdata.dao.impl;

import java.math.BigInteger;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Session;
import org.hibernate.engine.SessionFactoryImplementor;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.crmdp.dealer.coupon.constants.CouponConstants;
import com.cap.bts.crmdp.dealer.masterdata.dao.IMaintainDealerDao;
import com.cap.bts.crmdp.dealer.masterdata.domain.MasterDataDealerInfo;
import com.cap.bts.crmdp.dealer.masterdata.dto.DataFixPrcResultDto;
import com.cap.bts.crmdp.dealer.masterdata.dto.DealerChangeDto;
import com.cap.bts.crmdp.dealer.masterdata.dto.DealerQueryDto;
import com.cap.bts.crmdp.dealer.masterdata.dto.DmsDataFixResultDto;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.dao.callback.NativeQueryWithPagingJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;
import com.cap.bts.framework.common.util.Pager;
import com.cap.bts.framework.common.util.Utils;
import com.cap.bts.framework.common.util.WhereConditionGenerator;
import com.cap.bts.framework.usermgnt.util.PasswordUtil;



/**
 * 
 */
@Repository("maintainDealerDao")
public class MaintainDealerDaoImpl extends BaseDaoImpl<MasterDataDealerInfo, Long> implements IMaintainDealerDao {

	private static final Logger LOGGER = Logger.getLogger(MaintainDealerDaoImpl.class);
    /**
     * @Date        :      2015-7-15 
     * @param clazz   
     *
	*/
	@Inject
	public MaintainDealerDaoImpl(@Value("com.cap.bts.crmdp.dealer.masterdata.domain.MasterDataDealerInfo") Class<MasterDataDealerInfo> clazz) {
		super(clazz);
	}

	@Override
	public List<MasterDataDealerInfo> getMaintainDealerInfoByDto(DealerQueryDto dto) throws AppException {
		 String hql = "select info from MasterDataDealerInfo info";
		 String countHql = "select count(*) ";

		String fromStr = "from MasterDataDealerInfo info";
		String whereStr = " where 1=1 ";
		
		WhereConditionGenerator condition = new WhereConditionGenerator(whereStr);
		
		 if(Utils.notEmpty(dto.getDealerNum())){
			 condition.andLike("info.dealerNum", "", dto.getDealerNum(), "%");
		 }
		 
		 if(Utils.notEmpty(dto.getDealerName())){
			 condition.andLike("info.dealerName", "", dto.getDealerName(), "%");
		 }
		 
		 if(Utils.notEmpty(dto.getContactPhone())){
			 condition.andLike("info.contactPhone", "%", dto.getContactPhone(), "%");
		 }

		 if(Utils.notEmpty(dto.getDealerStatus())){
			 condition.and("info.dealerStatus", "=", dto.getDealerStatus());
		 }
		 
		 if(Utils.notNullAndNegative(dto.getId())){
			 condition.and("info.id", "=", dto.getId());
		 }
		
		 String orderHql = " order by info.id desc";
		 
		 Pager page = dto.getPager();
		 
		 page.setPageSize(5);//test
		
    	return getListByQueryWithPaging(hql + condition.toString()+ orderHql, countHql + fromStr + condition.toString(), dto.getPager());
	}

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年7月22日
     * @see com.cap.bts.crmdp.dealer.masterdata.dao.IMaintainDealerDao#getDealerByDealerNum(java.lang.String)
    **/
    @Override
    public MasterDataDealerInfo getDealerByDealerNum(String dealerNum) throws AppException {
        Map<String, Object> paraMap = new HashMap<String, Object>();
        paraMap.put("dealerNum", dealerNum);
        List<MasterDataDealerInfo> dealerInfo = getListByQueryWithMap("from MasterDataDealerInfo d where d.dealerNum = :dealerNum",
            paraMap);
        if (dealerInfo != null && !dealerInfo.isEmpty()) {
            return dealerInfo.get(0);
        } else {
            return null;
        }
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年7月26日
     * @see com.cap.bts.crmdp.dealer.masterdata.dao.IMaintainDealerDao#getDealerByOperationPwd(java.lang.String)
    **/
    @Override
    public MasterDataDealerInfo getDealerByOperationPwdAndDealerNum(String operationPwd,String dealerNum) throws AppException {
        Map<String,Object> paraMap = new HashMap<String, Object>();
        paraMap.put("operationPwd", PasswordUtil.encodePassword(operationPwd));
        paraMap.put("dealerNum", dealerNum);
        List<MasterDataDealerInfo> dealerInfo = getListByQueryWithMap("select d from MasterDataDealerInfo d where d.dealerNum =:dealerNum and d.operationPassword = :operationPwd",
            paraMap);
        if (dealerInfo != null && !dealerInfo.isEmpty()) {
            return dealerInfo.get(0);
        } else {
            return null;
        }
    }

    /**
     * {@inheritDoc} 
     * overridden:
     * @Date        :      2015年11月3日
     * @see com.cap.bts.crmdp.dealer.masterdata.dao.IMaintainDealerDao#getDealersByCode(java.lang.String)
    **/
    @Override
    public List<BigInteger> getDealersByCode(String code,String value) throws AppException {
        String sql = "select a.ID from tm_raw_dealer a where " + code + "= '" + value + "'";
        return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<BigInteger>(sql));
    }

	@Override
	public List<Object[]> exportMemberAndPackageFromDealer(String storeCode) throws AppException {
		String sql = "select * from vt_handct_member_export where store_code = '" + storeCode + "'";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}

	@Override
	public List<Object[]> exportPortfolioFromDealer(String storeCode) throws AppException {
		String sql = "select * from vt_handct_portfolio_export where store_code = '" + storeCode + "'";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}

	@Override
	public List<Object[]> exportMemberResultFromDealer(String storeCode) throws AppException {
		String sql = "select * from ti_mars_data_conversion_member_result where store_code = '" + storeCode + "'";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}

	@Override
	public List<Object[]> exportPortfolioResultFromDealer(String storeCode) throws AppException {
		String sql = "select * from ti_mars_data_conversion_portfolio_result where store_code = '" + storeCode + "'";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}

	@Override
	public List<Object[]> getAllDealerChange(DealerChangeDto dto) {
		if(dto != null){
			dto.getPager().setPageSize(10);
			StringBuilder sql = new StringBuilder();
			StringBuilder countSql = new StringBuilder();
			sql.append("select b.DEALER_NUM,b.DEALER_NAME,a.change_date from tm_mars_cutover_status a,tm_raw_dealer b"
					+ " where a.dealer_id = b.id");
			if(StringUtils.isNotBlank(dto.getDealerNum())){
				sql.append(" and dealer_id like '%" + dto.getDealerNum() + "%'");
			}
			if(StringUtils.isNotBlank(dto.getChangeStartDate())){
				sql.append(" and change_date >= '" + dto.getChangeStartDate() + "'");
			}
			if(StringUtils.isNotBlank(dto.getChangeEndDate())){
				sql.append(" and change_date <= '" + dto.getChangeEndDate() + "'");
			}
			countSql.append("select count(*) from("+ sql +") a");
			return this.getJpaTemplate().executeFind(
					new NativeQueryWithPagingJpaCallback<Object[]>(sql.toString(), countSql.toString(), dto.getPager()));
		}
		return null;
	}

	@Override
	public void updateChangeTimeFromDealerChange(String dealerNum,String changeDate) {
		String sql = "update tm_mars_cutover_status a set a.change_date = '" + changeDate + "'"
				+ "	where a.dealer_id = (select id from tm_raw_dealer b where b.DEALER_NUM = '"+ dealerNum +"')";
		this.getEntityManager().createNativeQuery(sql).executeUpdate();
	}

	@Override
	public MasterDataDealerInfo checkDealerExisted(Long dealerNum) {
		String sql = "select * from tm_mars_cutover_status where dealer_id = '" + dealerNum + "'";
		List<MasterDataDealerInfo> list = this.getListByNativeQuery(sql);
		if(list != null && !list.isEmpty()){
			return list.get(0);
		}else{
			return null;
		}
	}

	@Override
	public void saveDealerChanged(DealerChangeDto dealerChangeDto) {
		String sql = "INSERT INTO tm_mars_cutover_status(dealer_id,status,change_date,create_date,create_by,last_update_date,last_update_by)"
				+ " VALUES ('"+ dealerChangeDto.getDealerNum() +"','I','"+ dealerChangeDto.getChangeDate() +"','"
				+ new Timestamp(new Date().getTime()) +"','0','"+ new Timestamp(new Date().getTime()) +"','0')";
		this.getEntityManager().createNativeQuery(sql).executeUpdate();
	}

	@Override
	public String getDealersByProperty(String property,String dealerNum) throws AppException {
		if(CouponConstants.DEALER.equals(property)){
			property = CouponConstants.DEALER_NUM;
		}
		String sql = "select " + property + " from tm_raw_dealer where DEALER_NUM = '" + Long.valueOf(dealerNum) + "'";
		List<String> list = this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<String>(sql));
		if(list != null && !list.isEmpty()){
			return String.valueOf(list.get(0));
		}else{
			return null;
		}
	}

	@Override
	public void saveDmsDataFixFile(List<DmsDataFixResultDto> result) throws AppException {
		//打开链接
        Session session = (org.hibernate.Session) getEntityManager().getDelegate();
        SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
        Connection connection = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmt2 = null;

        try {
            LOGGER.info("start save DmsDataFixFile");

            connection = sf.getConnectionProvider().getConnection();
            connection.setAutoCommit(false);
            
            LOGGER.info("delete DmsDataFixFile");
            String sql = "delete from tm_dms_data_fix";
            pstmt2 = connection.prepareStatement(sql);
            pstmt2.executeUpdate();
            connection.commit();

            pstmt = connection.prepareStatement("INSERT INTO `tm_dms_data_fix` (`line_num`, `pcode`,"
            		+ " `member_code`, `member_name`, `mobile`, `mars_customer_code`, `car_plate`, `mars_car_no`,"
            		+ " `card_no`, `dms_card_no`, `card_type`, `dms_card_type`, `store_code`, `title`, `dms_title`,"
            		+ " `creation_date`, `validate_date`, `due_date`, `dp_status`, `dms_status`, `coupon_type`,"
            		+ " `free_service_times`, `times_of_use`, `times_left`)"
            		+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            for (int i = 0; i < result.size(); i++) {
            	DmsDataFixResultDto dto = result.get(i);
            	if(dto.getId() != null){
            		pstmt.setInt(1, Integer.valueOf(dto.getId()));
            	}else{
                	pstmt.setNull(1,java.sql.Types.INTEGER);
                }
            	pstmt.setString(2, dto.getPcode());
                pstmt.setString(3, dto.getMemberCode());
                pstmt.setString(4, dto.getMemberName());
                pstmt.setString(5, dto.getMobile());
                pstmt.setString(6, dto.getMarsCustomerCode());
            	pstmt.setString(7, dto.getCarPlate());
            	pstmt.setString(8, dto.getMarsCarNo());
            	pstmt.setString(9, dto.getCardNo());
            	pstmt.setString(10, dto.getDmsCardNo());
            	pstmt.setString(11, dto.getCardType());
            	pstmt.setString(12, dto.getDmsCardType());
            	pstmt.setString(13, dto.getStoreCode());
            	pstmt.setString(14, dto.getTitle());
            	pstmt.setString(15, dto.getDmsTitle());
            	if(dto.getCreationDate() != null){
                	try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
						pstmt.setDate(16, new java.sql.Date(sdf.parse(dto.getCreationDate()).getTime()));
					} catch (Exception e) {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						pstmt.setDate(16, new java.sql.Date(sdf.parse(dto.getCreationDate()).getTime()));
					}
                }else{
                	pstmt.setNull(16,java.sql.Types.DATE);
                }
            	if(dto.getValidateDate() != null){
                	try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
						pstmt.setDate(17, new java.sql.Date(sdf.parse(dto.getValidateDate()).getTime()));
					} catch (Exception e) {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						pstmt.setDate(17, new java.sql.Date(sdf.parse(dto.getValidateDate()).getTime()));
					}
                }else{
                	pstmt.setNull(17,java.sql.Types.DATE);
                }
            	if(dto.getDueDate() != null){
                	try {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
						pstmt.setDate(18, new java.sql.Date(sdf.parse(dto.getDueDate()).getTime()));
					} catch (Exception e) {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						pstmt.setDate(18, new java.sql.Date(sdf.parse(dto.getDueDate()).getTime()));
					}
                }else{
                	pstmt.setNull(18,java.sql.Types.DATE);
                }
            	pstmt.setString(19, dto.getStatus());
            	pstmt.setString(20, dto.getDmsStatus());
            	pstmt.setString(21, dto.getPreferentialType());
            	pstmt.setString(22, dto.getFreeRedeemTimes());

            	pstmt.setString(23, dto.getUseTimes());

            	pstmt.setString(24, dto.getUnUseTimes());
                pstmt.addBatch();
            }

            LOGGER.info(pstmt);
            LOGGER.info("start save commit");
            pstmt.executeBatch();     
            connection.commit();  
            LOGGER.info("end save commit");
        } catch (Exception e) {
            throw new AppException("ERROR", e);
        }finally{
            try {
                connection.setAutoCommit(true);
                pstmt.close();
                connection.close();
                LOGGER.info("end save DmsDataFixFile");
            } catch (SQLException e) {
                pstmt = null;
                connection = null;
            }
        }
	}

	@Override
	public List<DmsDataFixResultDto> getAllDmsData() {
		String sql = "select * from tm_dms_data_fix";
		List<Object[]> list = this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
		List<DmsDataFixResultDto> dtoList = new ArrayList<DmsDataFixResultDto>();
		if(list != null && !list.isEmpty()){
			for(Object[] obj : list){
				DmsDataFixResultDto dto = new DmsDataFixResultDto();
				dto.setId(String.valueOf(obj[1]));
				dto.setPcode(String.valueOf(obj[2]));
				dto.setMemberCode(String.valueOf(obj[3]));
				dto.setMemberName(String.valueOf(obj[4]));
				dto.setMobile(String.valueOf(obj[5]));
				dto.setMarsCustomerCode(String.valueOf(obj[6]));
				dto.setCarPlate(String.valueOf(obj[7]));
				dto.setMarsCarNo(String.valueOf(obj[8]));
				dto.setCardNo(String.valueOf(obj[9]));
				dto.setDmsCardNo(String.valueOf(obj[10]));
				dto.setCardType(String.valueOf(obj[11]));
				dto.setDmsCardType(String.valueOf(obj[12]));
				dto.setStoreCode(String.valueOf(obj[13]));
				dto.setTitle(String.valueOf(obj[14]));
				dto.setDmsTitle(String.valueOf(obj[15]));
				dto.setCreationDate(String.valueOf(obj[16]));
				dto.setValidateDate(String.valueOf(obj[17]));
				dto.setDueDate(String.valueOf(obj[18]));
				dto.setStatus(String.valueOf(obj[19]));
				dto.setDmsStatus(String.valueOf(obj[20]));
				dto.setFreeRedeemTimes(String.valueOf(obj[21]));
				dto.setUseTimes(String.valueOf(obj[22]));
				dto.setUnUseTimes(String.valueOf(obj[23]));
				dtoList.add(dto);
			}
		}
		return dtoList;
	}

	@Override
	public List<Object[]> getAllMessageForSend() throws AppException {
		String sql = "SELECT DEST_MOBILE,CONTENT FROM tm_sms_send";
		return this.getJpaTemplate().executeFind(new NativeQueryBySqlJpaCallback<Object[]>(sql));
	}

	@Override
	public DataFixPrcResultDto prcDmsDataFix() throws AppException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		CallableStatement proc = null;
		DataFixPrcResultDto dto = new DataFixPrcResultDto();
		try {
			connection = sf.getConnectionProvider().getConnection();
			proc = connection.prepareCall("{ call prc_dms_data_fix(?,?) }");
			proc.registerOutParameter(1, Types.VARCHAR);  
			proc.registerOutParameter(2, Types.VARCHAR);  
		    proc.execute();
		    dto.setReturnCode(proc.getString(1));
		    dto.setErrorMessage(proc.getString(2));
		    proc.close();
		} catch (SQLException e) {
			throw new AppException("ERROR", e);
		}finally{
			try {
				connection.close();
				LOGGER.info("end prc_dms_data_fix");
			} catch (SQLException e) {
				proc = null;
				connection = null;
			}
		}
		return dto;
	}

	@Override
	public void deleteDataFix() throws AppException {
		Session session = (org.hibernate.Session) getEntityManager().getDelegate();
		SessionFactoryImplementor sf = (SessionFactoryImplementor) session.getSessionFactory();
		Connection connection = null;
		PreparedStatement pstmt = null;
		
		try {
			connection = sf.getConnectionProvider().getConnection();
			connection.setAutoCommit(false);
			pstmt = connection.prepareStatement("delete from tm_dms_data_fix");
			
			LOGGER.info("start delete tm_dms_data_fix");
			pstmt.executeBatch();
			connection.commit();
			LOGGER.info("end delete tm_dms_data_fix");
			pstmt.clearBatch();
		} catch (Exception e) {
			throw new AppException("delete tm_dms_data_fix ERROR", e);
		}finally{
			try {
				connection.setAutoCommit(true);
				pstmt.close();
				connection.close();
				LOGGER.info("end delete tm_dms_data_fix");
			} catch (SQLException e) {
				pstmt = null;
				connection = null;
			}
		}
	}

}
